package com.bdwm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bdwm.db.DataBaseConn;
import com.bdwm.model.Constants;
import com.bdwm.model.Device;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class DeviveDBService {

	public static List<Device> getAllDeviceInfo() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Device> deviceList = new ArrayList<Device>();
		try {
			conn = DataBaseConn.mysqlConn();
			final String sql = "select device_name,device_assetnum,os_type,version_type,device_resolution,remark,device_dept,device_belongs from device_info";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();//执行SQL语句,仅仅用于查找語句
			while(rs.next()) {
				Device device = new Device();
//				device.setDeviceID(rs.getInt("device_id"));
				device.setDeviceName(rs.getString("device_name"));
				device.setDeviceAssetNum(rs.getString("device_assetnum"));
				device.setOsType(rs.getString("os_type"));
				device.setVersionType(rs.getString("version_type"));
				device.setDeviceResolution(rs.getString("device_resolution"));
				device.setRemark(rs.getString("remark"));
				device.setDeviceDept(rs.getString("device_dept"));
				device.setDeviceBelongs(rs.getString("device_belongs"));
				deviceList.add(device);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DataBaseConn.closeDB(rs, ps, conn);
		}
		return deviceList;
	}
	
	/**
	 * 
	 * @Description：删除设备
	 * @author：wangwenhui
	 * @time：2017年11月28日下午2:05:30
	 */
	public static int deleteDeviceInfo(String device_name) {
		Connection conn = null;
		PreparedStatement ps = null;
		int row = 0;
		try {
			conn = DataBaseConn.mysqlConn();//连接数据库
			String sql = "delete from device_info where device_name = ?";
			ps = conn.prepareStatement(sql); //创建statment并设置参数
			 //sql語句中寫了幾個字段，下面就必須要有幾個字段
			ps.setString(1, device_name);
			//处理结果集
			row = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DataBaseConn.closeDB(null, ps, conn);
		}
		return row;
	}
	
	/**
	 * 
	 * @Description：更新设备信息
	 * @author：wangwenhui
	 * @time：2017年11月28日下午3:44:42
	 */
	public static int updateDevice(Device device) {
		int row = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DataBaseConn.mysqlConn();
			String sql = "update device_info set device_assetnum=?,os_type=?,version_type=?,device_resolution=?,remark=?,device_dept=?,device_belongs=? where device_Name=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, device.getDeviceAssetNum());
			ps.setString(2, device.getOsType());
			ps.setString(3, device.getVersionType());
			ps.setString(4, device.getDeviceResolution());
			ps.setString(5, device.getRemark());
//			ps.setInt(6, device.getDeviceID());
			ps.setString(6, device.getDeviceDept());
			ps.setString(7, device.getDeviceBelongs());
			ps.setString(8, device.getDeviceName());
			//处理结果集
			row = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DataBaseConn.closeDB(null, ps, conn);
		}
		return row;
	}
	
	public static boolean findIfDeviceExist(String device_name) {
		boolean isExist = false;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				conn = DataBaseConn.mysqlConn();
				final String sql = "select count(1) as cnt from device_info where device_name = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, device_name);
				rs = ps.executeQuery();
				int cnt = 0;
				while (rs.next()) {
					cnt = rs.getInt("cnt");
				}
				if(cnt > 0) {
					isExist = true;
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DataBaseConn.closeDB(rs, ps, conn);
			}
		return isExist;
	}
	
	/**
	 * 
	 * @Description：增加设备接口
	 * @author：wangwenhui
	 * @time：2017年11月28日下午6:39:10
	 */
	public static int addDevice(Device device) {
		int row = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DataBaseConn.mysqlConn();
			String sql = "insert into device_info(device_name,device_assetnum,os_type,version_type,device_resolution,remark,device_dept,device_belongs) values(?,?,?,?,?,?,?,?)";
			conn.setAutoCommit(false);//将此连接的自动提交模式设置为给定状态。false为禁用
			ps = conn.prepareStatement(sql);//SQL 语句被预编译并存储在 PreparedStatement 对象中
			ps.setString(1, device.getDeviceName());
			ps.setString(2, device.getDeviceAssetNum());
			ps.setString(3, device.getOsType());
			ps.setString(4, device.getVersionType());
			ps.setString(5, device.getDeviceResolution());
			ps.setString(6, device.getRemark());
			ps.setString(7, device.getDeviceDept());
			ps.setString(8, device.getDeviceBelongs());
			row = ps.executeUpdate();
			if(row != 0){ 
				row = Constants.SUCCESS; 
				conn.commit();//使所有上一次提交/回滚后进行的更改成为持久更改，并释放此 Connection 对象当前持有的所有数据库锁
			}else{
				row = Constants.ERROR; 
				conn.rollback();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DataBaseConn.closeDB(null, ps, conn);
		}
		return row;
	}
	
	/**
	 * 
		 * @Description：添加设备的时候看设备是否存在
		 * @author：wangwenhui
		 * @time：2017年11月29日上午11:25:46
	 */
	public static boolean deviceIsExist(Device device) {
		boolean isExist = false;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				conn = DataBaseConn.mysqlConn();
				final String sql = "select count(1) as cnt from device_info where device_name=? and device_assetnum=? and os_type=? and version_type=? and device_resolution=?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, device.getDeviceName());
				ps.setString(2, device.getDeviceAssetNum());
				ps.setString(3, device.getOsType());
				ps.setString(4, device.getVersionType());
				ps.setString(5, device.getDeviceResolution());
				rs = ps.executeQuery();
				int cnt = 0;
				while (rs.next()) {
					cnt = rs.getInt("cnt");
				}
				if(cnt > 0) {
					isExist = true;
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DataBaseConn.closeDB(rs, ps, conn);
			}
		return isExist;
	}
	
	public static void main(String[] args) {
		//添加设备
	/*	Device device  = new Device();
		device.setDeviceName("32323232");
		device.setDeviceAssetNum("uiu32o");
		device.setOsType("4u3");
		device.setVersionType("jfka");
		device.setDeviceResolution("hfalf");
		device.setRemark("jjjjjjjjjj");
		device.setDeviceDept("jjjjjjjjjj");
		device.setDeviceBelongs("jjjjjjjjjj");
		
		
		boolean isExist = DeviveDBService.findIfDeviceExist(device.getDeviceName());
		System.out.println(device.getDeviceName());
		JSONObject json = new JSONObject();
		if(isExist) {
			json.accumulate("status", 1);
			json.accumulate("message", "device already exist!!");
			System.out.println(json.toString());
			System.out.println("device already exist");
			return;
		}
		json.accumulate("status", 0);
		json.accumulate("message", "add device successed!!");
		System.out.println(json.toString());
		System.out.println(DeviveDBService.addDevice(device));*/
		//更新设备
		Device device  = new Device();
		device.setDeviceName("test_2");
		device.setDeviceAssetNum("43");
		device.setOsType("tom");
		device.setVersionType("893.00");
		device.setDeviceResolution("jelf;jf");
		device.setRemark("阿弥陀佛阿弥陀佛阿弥陀佛阿弥陀佛");
		device.setDeviceDept("阿弥陀佛阿弥陀佛阿弥陀佛阿弥陀佛");
		device.setDeviceBelongs("阿弥陀佛阿弥陀佛阿弥陀佛阿弥陀佛");
//		device.setDeviceID(10009);
		System.out.println(DeviveDBService.updateDevice(device));
		
		
		//获取所有设备信息
		
		/*DeviveDBService des = new DeviveDBService();
		List<Device> deviceList = des.getAllDeviceInfo();
		JSONObject json = new JSONObject();
		json.accumulate("status", 0);
		JSONArray jsonArr = new JSONArray();
		for(Device device1: deviceList) {
			JSONObject deviceJson = new JSONObject();
			deviceJson.accumulate("deviceName", device1.getDeviceName());
			deviceJson.accumulate("deviceAssetNum", device1.getDeviceAssetNum());
			deviceJson.accumulate("osType", device1.getOsType());
			deviceJson.accumulate("versionType", device1.getVersionType());
			deviceJson.accumulate("deviceResolution", device1.getDeviceResolution());
			deviceJson.accumulate("remark", device1.getRemark());
			deviceJson.accumulate("deviceDept", device1.getDeviceDept());
			deviceJson.accumulate("deviceBelongs", device1.getDeviceBelongs());
			jsonArr.add(deviceJson);
		}
		json.accumulate("data", jsonArr);
		System.out.println(json.toString());*/
		
		//删除设备
	/*	int row = new DeviveDBService().deleteDeviceInfo("test_1");
		JSONObject json1 = new JSONObject();
		json1.accumulate("status", 0);
		json1.accumulate("desc", "delete device success");
		if (row > 0)
		System.out.println(json1.toString());*/
		
		
		
	}
}
